package com.navi;

import com.navi.entity.ColumnEntity;
import com.navi.entity.TableEntity;
import com.navi.exception.HmsException;
import com.navi.util.TableJdbcUtil;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
import java.util.stream.Stream;

/**
 * Created by Lin.Xin on 2017/10/7.
 */
@Service
public class FileLoader {

    @Value("${gp.datasource.drive}")
    private String driver;
    @Value("${gp.datasource.url}")
    private String url;
    @Value("${gp.datasource.username}")
    private String username;
    @Value("${gp.datasource.password}")
    private String password;
    @Value("${local.csv.path}")
    private String localCsvPath;
    @Value("${gpfdist.host}")
    private String gpfdistHost;
    @Value("${gpfdist.port}")
    private String gpfdistPort;

    private Logger logger = LoggerFactory.getLogger(this.getClass());

    public void saveFile2GreenPlum(Map<String, String> tableFileMap) {
        tableFileMap.forEach((tableName, fileName) -> {
            String extTableName = null;
            //get connection
            try (Connection connection = TableJdbcUtil.getConnection(driver, url, username, password)) {

                Statement stmt = connection.createStatement();
                //get table space name
                String nspName = TableJdbcUtil.getTableNspName(tableName, stmt);
                //create external table
                final TableEntity tableMeteInfo = createExtTableProc(connection, stmt, nspName, tableName, fileName);
                extTableName = tableMeteInfo.getExtTableName();
                tableMeteInfo.setNspName(nspName);
                logger.info("create external table {} successful ", extTableName);
                if (StringUtils.isNotBlank(extTableName)) {
                    //insert into gp_table select * from ext_table
                    addDataFromEXT(stmt, nspName, tableName, tableMeteInfo);

                }
                logger.info("{} has been saved to GreenPlum ", fileName);
            } catch (Exception e) {
                e.printStackTrace();
                logger.error(e.toString());
                throw new HmsException(e);
            } finally {
                if (StringUtils.isNotEmpty(extTableName)) {
                    dropExtTableProc(extTableName);
                    logger.info("drop external table {} successful ", extTableName);
                }

            }
        });

    }

    private void dropExtTableProc(String extTblName) {
        try (Connection connection = TableJdbcUtil.getConnection(driver, url, username, password)) {
            Statement stmt = connection.createStatement();
            try {
                String dropDdl = String.format("DROP EXTERNAL TABLE IF EXISTS %s ", extTblName);
                stmt.executeUpdate(dropDdl);
            }catch (Exception e){
                throw e;
            }finally {
                stmt.close();
            }
        } catch (Exception ex) {
            ex.printStackTrace();
            logger.error(ex.toString());
        }

    }

    public static void main(String[] args) throws IOException {
        Files.move(Paths.get("/usr/local/loader/data/completed.C_LOI.m_bis_data_d.20171013095153.csv"),
                Paths.get("/usr/local/loader/backup/completed.C_LOI.m_bis_data_d.20171013095153.csv"));
    }

    private void addDataFromEXT(Statement stmt, String nspName, String tableName, TableEntity tableEntity) throws SQLException {
        String sql = null;
        final List<ColumnEntity> columnEntityList = tableEntity.getColumnEntityList();
        final Map<Boolean, List<ColumnEntity>> columnMap = columnEntityList.stream().collect(Collectors.groupingBy(ColumnEntity::getPrimaryKeyFlg));
        final List<ColumnEntity> matchColumnList = columnMap.get(true);
        final List<ColumnEntity> updateColumnList = columnMap.get(false);

        if (tableEntity.getHasPrimaryKey()) {
            mergeProc(stmt,tableEntity);
            // mergeProc()
            //create stage table
            //insert into stage_table select * from external_table

            //insert into qualified_table select * from stage_table where not exist in qualified

            //update qualified_table into_table set column = from_table.column and ...
            //from stage_table from_table where into_table.column = from_table.column and ...
            StringBuffer sqlBuffer = new StringBuffer();
            sqlBuffer.append("UPDATE ").append(nspName).append(".").append("tableName into_table");
            sqlBuffer.append(matchColumnList.stream()
                    .map(c -> String.format("%s=%s", c.getColumnName(), "from_table." + c.getColumnName()))
                    .collect(Collectors.joining(",")));
            sqlBuffer.append(" FROM ").append(tableEntity.getExtTableName()).append(" from_table");

            sqlBuffer.append(" WHERE ");
            updateColumnList.stream()
                    .map(ColumnEntity::getColumnName)
                    .map(c -> String.format("into_table.%s = from_table.%s",c,c))
                    .collect(Collectors.joining(" and "));
        } else {
            sql = String.format("INSERT INTO %s.%s  SELECT * FROM %s", nspName, tableName, tableEntity);
        }

        stmt.executeUpdate(sql);
    }

    private void mergeProc(Statement stmt, TableEntity tableEntity) {
        //向stageTable中写入数据
        createStageTable(stmt,tableEntity);
        doInsert(tableEntity.getStageTableName());

        doUpdate(tableEntity);

        doInsertNotExist(tableEntity);

    }

    private void doInsertNotExist(TableEntity tableEntity) {

    }

    private void doUpdate(TableEntity tableEntity) {

    }

    private void doInsert(String stageTableName) {
    }

    private void createStageTable(Statement stmt, TableEntity tableEntity) {
        String stageTableName = null;
        tableEntity.setStageTableName(stageTableName);
    }

    private TableEntity createExtTableProc(Connection connection, Statement stmt, String nspName, String tableName, String fileLocation) throws SQLException {
        String strTime = fileLocation.split("\\.")[3];

        //drop table if exist
        String dropDdl = String.format("DROP EXTERNAL TABLE IF EXISTS ext_%s_%s", tableName, strTime);

        stmt.executeUpdate(dropDdl);


        //get table meta information
        final TableEntity tableMetaInfo = TableJdbcUtil.getTableMetaInfo(nspName, tableName, stmt);
        //创建表
        String extTable = createExtTable(tableName, fileLocation, tableMetaInfo.getColumnEntityList(), stmt);
        tableMetaInfo.setExtTableName(extTable);
        return tableMetaInfo;

    }

    /**
     * 创建外部表并返回表名
     *
     * @param tableName
     * @param fileLocation
     * @param columnEntityList
     * @param stmt
     * @return
     */
    public String createExtTable(String tableName, String fileLocation, List<ColumnEntity> columnEntityList, Statement stmt) {
        final String[] fileNamingArr = fileLocation.split("\\.");
        String strTime = fileNamingArr[3];
        String queueName = fileNamingArr[1];
        String extTable = "public.ext_" + queueName + "_" + tableName + "_" + strTime;

        //获取本机IP
        GetIP local = new GetIP();
        String localIP = local.getCurrentIp();
//        String location = String.format("gpfdist://%s:%s/%s", gpfdistHost, gpfdistPort, fileLocation.split("/")[5]);
        String location = String.format("gpfdist://%s:%s/%s", localIP, gpfdistPort, fileLocation.split("/")[5]);

        String columnString = columnEntityList.stream().map(cl ->
                cl.getColumnName() + " " + cl.getDataType()
        ).collect(Collectors.joining(","));


        StringBuffer createTableBuffer = new StringBuffer("create external table ");
//        createTableBuffer.append("public.ext_" + tableName).append("_").append(strTime);
        createTableBuffer.append(extTable);
        createTableBuffer.append("(").append(columnString).append(")");
        createTableBuffer.append(" location ('").append(location).append("").append("')");
        createTableBuffer.append(" FORMAT 'text' (delimiter ',' null '\\\\N' escape '\\\\')");
        createTableBuffer.append(" ENCODING 'UTF8';");

        try {
            stmt.executeUpdate(createTableBuffer.toString());
        } catch (SQLException e) {
            e.printStackTrace();
            logger.error(e.toString());
        }

        //返回外部表名供insert
        return extTable;
    }




}
